package com.surfacing.rcp.db.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.surfacing.rcp.db.DataSource;
import com.surfacing.rcp.db.Queries;
import com.surfacing.rcp.db.dao.UserDAO;
import com.surfacing.rcp.model.entity.User;

public class UserDAOMySQL implements UserDAO {
	// The data source to get the connection and the queries file
	private final DataSource source = DataSource.getInstance();
	private final Queries queries = Queries.getInstance();

	// the dependent dao classes

	@Override
	public boolean addUser(User user) throws SQLException {
		Connection connection = source.getConnection();
		try {
			final PreparedStatement query = connection.prepareStatement(queries
					.getStatment("insert.StaffUser"));
			query.setString(1, user.getUserName());
			query.setString(2, user.getFirstName());
			query.setString(3, user.getLastName());
			if (query.executeUpdate() == 0)
				return false;
			return true;
		} finally {
			connection.close();
		}
	}

	@Override
	public boolean updateUser(User user) throws SQLException {
		Connection connection = source.getConnection();
		try {
			// primaryLocation = ?, firstname = ?, lastname = ?, sex = ?,
			// birthday = ?, email = ?, street = ?, city = ?, username = ? where
			// user_ID = ?;
			final PreparedStatement query1 = connection
					.prepareStatement(queries.getStatment("update.StaffUser"));
			query1.setInt(4, user.getStaffMemberId());
			query1.setString(1, user.getUserName());
			query1.setString(2, user.getFirstName());
			query1.setString(3, user.getLastName());

			// check if the update was successfully
			if (query1.executeUpdate() == 0)
				return false;
			return true;
		} finally {
			connection.close();
		}
	}

	public List<User> getAllUsers() throws SQLException {
		Connection connection = source.getConnection();
		try {
			// u.username, e.primaryLocation, lo.locationname, e.user_ID,
			// e.firstname, e.lastname, e.sex, e.birthday, e.email,
			// *u.authorization, *u.isloggedin, *u.locked, e.city, e.street
			final PreparedStatement query = connection.prepareStatement(queries
					.getStatment("list.StaffUser"));
			final ResultSet rs = query.executeQuery();
			// create the staff list and loop over the result
			List<User> users = new ArrayList<User>();
			while (rs.next()) {
				User staff = new User();
				staff.setStaffMemberId(rs.getInt("u.id"));
				staff.setLastName(rs.getString("u.lastName"));
				staff.setFirstName(rs.getString("u.firstName"));
				staff.setUserName(rs.getString("u.username"));
				// query and set the location, phone and competence
				users.add(staff);
			}
			return users;
		} finally {
			connection.close();
		}
	}

	public User getUserByID(int id) throws SQLException {
		Connection connection = source.getConnection();
		try {
			// u.username, e.primaryLocation, lo.locationname, e.user_ID,
			// e.firstname, e.lastname, e.sex, e.birthday, e.email,
			// *u.authorization, *u.isloggedin, *u.locked, e.city, e.street
			final PreparedStatement query = connection.prepareStatement(queries
					.getStatment("get.userByID"));
			query.setInt(1, id);
			final ResultSet rs = query.executeQuery();
			// assert we have a result
			if (rs.first()) {
				User staff = new User();
				staff.setStaffMemberId(rs.getInt("u.id"));
				staff.setLastName(rs.getString("u.lastName"));
				staff.setFirstName(rs.getString("u.firstName"));
				staff.setUserName(rs.getString("u.username"));
				// query and set the location, phone and competence
				return staff;
			}
			// no result set
			return null;
		} finally {
			connection.close();
		}
	}

	public User getUserByUsername(String username) throws SQLException {
		Connection connection = source.getConnection();
		try {
			// u.username, e.primaryLocation, lo.locationname, e.user_ID,
			// e.firstname, e.lastname, e.sex, e.birthday, e.email,
			// *u.authorization, *u.isloggedin, *u.locked, e.city, e.street
			final PreparedStatement query = connection.prepareStatement(queries
					.getStatment("get.userbyUsername"));
			query.setString(1, username);
			final ResultSet rs = query.executeQuery();
			if (rs.first()) {
				User staff = new User();
				staff.setStaffMemberId(rs.getInt("u.id"));
				staff.setLastName(rs.getString("u.lastName"));
				staff.setFirstName(rs.getString("u.firstName"));
				staff.setUserName(rs.getString("u.username"));
				// query and set the location, phone and competence
				return staff;
			}
			// no result set
			return null;
		} finally {
			connection.close();
		}
	}
}